package com.house365.core.util.store;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.house365.core.bean.NotificationDataRec;
import com.house365.core.bean.NotificationDataRec.PushTable;
import com.house365.core.constant.CorePreferences;
import com.house365.core.json.JSONArray;
import com.house365.core.json.JSONObject;
import com.house365.core.reflect.ReflectUtil;
import com.house365.core.util.TextUtil;
import com.house365.core.util.db.DataBaseOpenHelper;
import com.house365.core.util.db.DataBaseOpenHelper.DataBaseOpenListener;
import com.house365.core.util.db.DataBaseService;
import com.house365.core.util.db.DataBaseService.DBQuery;

public class CacheDBService {
	private final int VERSION = 2;
	private final String DB_NAME = "tf_cache";
	private final String TABLE_NAME = "table_cache";

	private DataBaseService dbService;
	private static CacheDBService instance;

	/**推送的消息的过期时间，设置为7天
     * <p>格式："-1 minute"，"+1 minute"，"-1 day"，"+1 day"，
     * "-1 month"，"+1 month"，"-1 year"，"+1 year"等
     * <p>具体参考Sqlite文档
     * */
   private static final String PUSH_OUT_OF_DATE = "-7 day";
     
	//推送消息单独存放在一张表里
	public CacheDBService(Context context) {
		dbService = new DataBaseService(context, DB_NAME, VERSION,
				new DataBaseOpenListener() {

					@Override
					public void onVersionUpgrade(SQLiteDatabase db, int oldVersion,
							int newVersion) {
					    if (oldVersion == 1 && newVersion == 2) {
					        //版本1中没有推送消息的表，需要创建
					        db.execSQL(NotificationDataRec.PUSH_TABLE_DELETE);
					        db.execSQL(NotificationDataRec.PUSH_TABLE_CREATE);
                        }
					}

					@Override
					public String[] getIndexSql() {
						return null;
					}

					@Override
					public String[] getCreateSql() {
						String[] createSql = new String[2];
						createSql[0] = "CREATE TABLE IF NOT EXISTS "
								+ TABLE_NAME
								+ "(cahce_key text UNIQUE,cache_value text)";
						//推送消息，单独创建一张表
						createSql[1] = NotificationDataRec.PUSH_TABLE_CREATE;
						return createSql;
					}
				});
	}

	public static CacheDBService getInstance(Context context) {
		if (null == instance) {
			instance = new CacheDBService(context);
		}

		return instance;
	}

	public void addCache(String key, String value) {
		DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
		SQLiteDatabase db = dbo.getWritableDatabase();
		db.execSQL("replace into " + TABLE_NAME
				+ "(cahce_key, cache_value) values(?,?)", new Object[] {
				TextUtil.getUTF8(key), TextUtil.getUTF8(value) });
	}

	public String getCache(String key) {
		return (String) dbService.query("select cache_value from " + TABLE_NAME
				+ " where cahce_key = '" + key + "'", null, new DBQuery() {

			@Override
			public Object onQueryResult(Cursor cursor) {
				String value = null;
				if (cursor.moveToNext()) {
					value = cursor.getString(0);
				}
				cursor.close();
				return value;
			}
		});
	}

	public void deleteCache(String key) {
		DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
		SQLiteDatabase db = dbo.getWritableDatabase();
		db.delete(TABLE_NAME, "cahce_key=?", new String[] { key });
	}
	
	public void clearCache() {
		DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
		SQLiteDatabase db = dbo.getWritableDatabase();
		db.execSQL("delete from " + TABLE_NAME, new String[]{});
	}
	
	   /** 添加一条推送消息*/
    public void addPushCache(ContentValues values) {
        if (values == null) {
            return;
        }
        DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
        SQLiteDatabase db = dbo.getWritableDatabase();
        db.replace(PushTable.TABLE_NAME, null, values);
    }

    /** 获取一条推送消息*/
    public NotificationDataRec getPushCache(String id, String city) {
        if (id == null) {
            return null;
        }
        return (NotificationDataRec) dbService.query(
                "select * from " + PushTable.TABLE_NAME + 
                " where " + PushTable.COLUMN_NAME_PUSH_ID +" = '" + id + 
                "' and " + PushTable.COLUMN_NAME_CITY +" = '" + city + "'", null, new DBQuery() {

            @Override
            public Object onQueryResult(Cursor cursor) {
                Object value = null;
                //在createFromCursor函数中调用cursor.moveToNext()
                //此函数内不需调用
                value = NotificationDataRec.createFromCursor(cursor);
                cursor.close();
                return value;
            }
        });
    }
    
    /** 获取所有推送消息，并按照降序排列*/
    @SuppressWarnings("unchecked")
    public ArrayList<NotificationDataRec> getAllPushCache(String city) {
        
        return (ArrayList<NotificationDataRec>) dbService.query(
                "select * from " + PushTable.TABLE_NAME + 
                " where " + PushTable.COLUMN_NAME_CITY + " = '" + city + "'" + //推送消息的城市
                " order by " + PushTable.COLUMN_NAME_TIMESTAMP + " desc",//时间降序排列
                null, 
                new DBQuery() {

            @Override
            public Object onQueryResult(Cursor cursor) {
                Object value = NotificationDataRec.createListFromCursor(cursor);
                cursor.close();
                return value;
            }
        });
    }
    
    /** 删除一条推送消息*/
    public void deletePushCache(String id, String city) {
        if (id == null) {
            return;
        }
        DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
        SQLiteDatabase db = dbo.getWritableDatabase();
        db.delete(PushTable.TABLE_NAME, 
                PushTable.COLUMN_NAME_PUSH_ID + 
                "=? and " + PushTable.COLUMN_NAME_CITY + "=? ", 
                new String[] { id, city});
    }
    
    /** 清空所有推送消息*/
    public void clearPushCache() {
        DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
        SQLiteDatabase db = dbo.getWritableDatabase();
        db.execSQL("delete from " + PushTable.TABLE_NAME, new String[]{});
    }
    
    /** 判断推送消息是否已读*/
    public boolean isNotificationAlreadyRead(String id, String city) {
        NotificationDataRec notification = getPushCache(id, city);
        if (notification == null || notification.getAlreadyRead() == 0) {
            return false;
        } else {
            return true;
        }
    }
    
    /** 标记推送消息已读*/
    public void markNotificationIsAlreadyRead(String id, String city) {
        if (id == null) {
            return;
        }
        DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
        SQLiteDatabase db = dbo.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(PushTable.COLUMN_NAME_ALREADY_READ, 1);
        db.update(PushTable.TABLE_NAME, 
                values, 
                PushTable.COLUMN_NAME_PUSH_ID + " = ? and " + PushTable.COLUMN_NAME_CITY + " = ?", 
                new String[]{id, city});
    }
    /** 删除过期的推送消息*/
    public void deleteOutOfDatePushCache() {
        DataBaseOpenHelper dbo = dbService.getDbOpenHelper();
        SQLiteDatabase db = dbo.getWritableDatabase();
        db.delete(PushTable.TABLE_NAME, 
                PushTable.COLUMN_NAME_TIMESTAMP 
                + " < datetime('now', '" + PUSH_OUT_OF_DATE + "', 'localtime') ", 
                null);
    }
    
    /** 获取过期的推送消息*/
    @SuppressWarnings("unchecked")
    public ArrayList<NotificationDataRec> getOutOfDatePushCache() {
        return (ArrayList<NotificationDataRec>) dbService.query(
                "select * from " + PushTable.TABLE_NAME + 
                " where " + PushTable.COLUMN_NAME_TIMESTAMP + 
                " < datetime('now', '" + PUSH_OUT_OF_DATE + "', 'localtime') ",
                null,
                new DBQuery() {

            @Override
            public Object onQueryResult(Cursor cursor) {
                Object value = NotificationDataRec.createListFromCursor(cursor);
                cursor.close();
                return value;
            }
        });
    }
    
    /** 获取当天的推送消息*/
    @SuppressWarnings("unchecked")
    public ArrayList<NotificationDataRec> getCurrentDatePushCache(String city) {
        return (ArrayList<NotificationDataRec>) dbService.query(
                "select * from " + PushTable.TABLE_NAME + 
                " where " + 
                        "date(" + PushTable.COLUMN_NAME_TIMESTAMP + ") = date('now', 'localtime') and " + 
                        PushTable.COLUMN_NAME_CITY + " = '" + city + "'",
                null,
                new DBQuery() {

            @Override
            public Object onQueryResult(Cursor cursor) {
                Object value = NotificationDataRec.createListFromCursor(cursor);
                cursor.close();
                return value;
            }
        });
    }
	
	public <T extends SharedPreferencesDTO> boolean hasListItem(String key, T t){
		boolean flag = false;
		try {
			JSONArray infoArray = getList(key);
			
			for (int i = 0; i < infoArray.length(); i++) {
				T temp = (T) ReflectUtil.copy(t.getClass(),
						infoArray.getJSONObject(i));
				if (t.isSame(temp)) {
					flag = true;
					break;
				}
			}
		} catch (Exception e) {
			CorePreferences.ERROR(e);
		}
		return flag;
	}
	
	public <T extends SharedPreferencesDTO> List<T> getListWithCast(T t,
			String key){
		List<T> list = new ArrayList<T>();
		try {
			JSONArray jsonArray = getList(key);
			T temp;
			for (int i = 0; i < jsonArray.length(); i++) {
				temp = (T) ReflectUtil.copy(t.getClass(),
						jsonArray.getJSONObject(i));
				list.add(temp);
			}
		} catch (Exception e) {
			CorePreferences.ERROR(e);
		}
		
		return list;
	}
	
	public <T extends SharedPreferencesDTO> void removeListItem(String key, T t) {
		try {
			JSONArray infoArray = getList(key);
			int flag = -1;
			for (int i = 0; i < infoArray.length(); i++) {
				T temp = (T) ReflectUtil.copy(t.getClass(),
						infoArray.getJSONObject(i));
				if (t.isSame(temp)) {
					flag = i;
					break;
				}
			}
			if (flag != -1) {
				infoArray.remove(flag);
				addCache(key, infoArray.toString());
			}
		} catch (Exception e) {
			CorePreferences.ERROR(e);
		}
	}
	
	public <T> void addListItem(String key, T t) {
		JSONArray array = getList(key);
		array.put(new JSONObject(t));
		addCache(key, array.toString());
	}
	
	public void putList(String key, List list) {
		addCache(key, new JSONArray(list).toString());
	} 
	
	public JSONArray getList(String key) {
		JSONArray oldInfoArray;
		try {
			String oldInfo = getCache(key);
			if (oldInfo == null) {
				oldInfoArray = new JSONArray();
			} else {
				oldInfoArray = new JSONArray(oldInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
			oldInfoArray = new JSONArray();
		}
		return oldInfoArray;
	}
}
